package com.zzptc.easyshop.dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.zzptc.easyshop.db.IDB;
import com.zzptc.easyshop.db.MysqlDB;
import com.zzptc.easyshop.vo.Users;
import com.zzptc.easyshop.vo.VO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao implements GoodTypeDaoImp.IDAO {
    @Override
    public boolean insert(VO vo) {
        MysqlDB db = new MysqlDB();
        Connection conn =null;
        //执行游标
        PreparedStatement ps = null;
        int i = 0;
        try {
            ps = db.getConn().prepareStatement("insert into easybuy_user(eu_user_name,eu_password) values(?,?)");
            //创建users类对象
            Users users = (Users) vo;
            //为问号赋值
            ps.setString(1, users.getEu_user_name());
            ps.setString(2, users.getEu_password());
            //执行sql语句，成功即返回1
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接

            try {
                ps.close();
                db.closeAll(conn,ps,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return i > 0;
    }

    @Override
    public boolean login(VO vo) {
        MysqlDB db = new MysqlDB();
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean i = false;
        try {
            ps = db.getConn().prepareStatement("select * from easybuy_user where eu_user_id=? and eu_password=? and eu_status=?");
            Users users = (Users) vo;
            ps.setString(1, users.getEu_user_id());
            ps.setString(2, users.getEu_password());
            ps.setInt(3,users.getEu_status());
            //执行sql语句
            rs = ps.executeQuery();
            i = rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {

                db.closeAll(conn,ps,rs);

            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        //返回是否查询到内容
        return i;
    }

    @Override
    public boolean delete(VO vo){
        IDB db=new MysqlDB();
        Connection conn=null;
        PreparedStatement ps=null;
        int i=0;
        try {
            conn=db.getConn();
            ps=conn.prepareStatement("delete from easybuy_user where eu_user_id=?");
            Users users= (Users) vo;
            ps.setString(1,users.getEu_user_id());
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                db.closeAll(conn,ps,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return i>0;
    }

    @Override
    public boolean updata(VO vo){
        IDB db=new MysqlDB();
        Connection conn=null;
        PreparedStatement ps=null;
        int i=0;
        try {
            conn=db.getConn();
            ps=conn.prepareStatement("update easybuy_user set eu_user_id=?,eu_user_name=?,eu_password=?,eu_sex=?,eu_birthday=?,eu_mobile=?,eu_address=?,eu_lj=? where eu_user_id=?");
            Users users= (Users) vo;
            ps.setString(1,users.getEu_user_id());
            ps.setString(2,users.getEu_user_name());
            ps.setString(3,users.getEu_password());
            ps.setString(4,users.getEu_sex());
            ps.setString(5,users.getEu_birthday());
            ps.setString(6,users.getEu_mobile());
            ps.setString(7,users.getEu_address());
            ps.setString(8,users.getEu_lj());
            ps.setString(9,users.getEu_user_id());
            i=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                db.closeAll(conn,ps,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i>0;
    }


    @Override
    public List getAll(){
        //定义集合，准备放Users对象
        List<Users> list=new ArrayList<Users>();
        IDB db=new MysqlDB();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        try {
            conn=db.getConn();
            String sql="select * from easybuy_user";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            //循环遍历虚拟表 放入集合对象
            while (rs.next()){
                Users u = new Users();

                u.setEu_user_id(rs.getString(1));
                u.setEu_user_name(rs.getString(2));
                u.setEu_password(rs.getString(3));
                u.setEu_sex(rs.getString(4));
                u.setEu_birthday(rs.getString(5));
                u.setEu_identity_code(rs.getString(6));
                u.setEu_email(rs.getString(7));
                u.setEu_mobile(rs.getString(8));
                u.setEu_address(rs.getString(9));
                u.setEu_status(rs.getInt(10));
                list.add(u);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //关闭数据库连接   释放资源

            try {
                db.closeAll(conn,ps,rs);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    public Users getUsersByID(String id) throws SQLException{


        IDB db = new MysqlDB();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Users u=null;
        try {
            conn = db.getConn();
            String sql = "select * from easybuy_user where eu_user_id=?";
            ps=conn.prepareStatement(sql);

            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();
            //循环遍历虚拟表 放入集合对象
            if (rs.next()) {
                u = new Users();

                u.setEu_user_id(rs.getString(1));
                u.setEu_user_name(rs.getString(2));
                u.setEu_password(rs.getString(3));
                u.setEu_sex(rs.getString(4));
                u.setEu_birthday(rs.getString(5));
                u.setEu_identity_code(rs.getString(6));
                u.setEu_email(rs.getString(7));
                u.setEu_mobile(rs.getString(8));
                u.setEu_address(rs.getString(9));
                u.setEu_status(rs.getInt(10));
//                return u;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭数据库连接   释放资源

            try {
                db.closeAll(conn, ps, rs);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return u;
    }

    public boolean isUserID(String id)throws SQLException{
        IDB db = new MysqlDB();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Users u=null;
        boolean i=false;
        try {
            conn = db.getConn();
            String sql = "select * from easybuy_user where eu_user_id=?";
            ps=conn.prepareStatement(sql);

            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs=ps.executeQuery();
            i=rs.next();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(conn,ps,rs);
        }
        return i;
    }
    public static void main(String[] args) {
        //将list转换成json
        UserDao userDao=new UserDao();
        List<VO> listall=(ArrayList)userDao.getAll();
        //JSON包中的toJSONString方法     传入ArrayList集合
        String j=JSON.toJSONString(listall);
        System.out.println(j);
        List<Users> test=JSONObject.parseArray(j,Users.class);
        //将JONS转为List
        System.out.println(test.size());
        try {

            System.out.println(userDao.isUserID("ddd"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
